const { getDbInstance } = require("./database.js");

// 获取指定用户和groupDeviceId的所有上传条目
const getUserDownloadsByDeviceId = (
  {
    userid,
    groupDeviceId,
    statusArr,
    orderBy = "taskid",
    orderDirection = "ASC",
    page = 1,
    pageSize = 1000,
  } = {},
  callback
) => {
  getDbInstance().serialize(() => {
    let sql = "SELECT * FROM downloads WHERE userid = ? AND groupDeviceId = ?";
    let countSql =
      "SELECT COUNT(*) as total FROM downloads WHERE userid = ? AND groupDeviceId = ?";
    let params = [userid, groupDeviceId];

    if (!groupDeviceId) {
      sql = sql.replace(" AND groupDeviceId = ?", "");
      params = [userid];
      countSql = countSql.replace(" AND groupDeviceId = ?", "");
    }

    if (statusArr && statusArr.length > 0) {
      // Prepare the placeholder for each status
      const placeholders = statusArr.map(() => "?").join(",");
      sql += ` AND status IN (${placeholders})`;
      countSql += ` AND status IN (${placeholders})`;
      params = [...params, ...statusArr];
    }

    const countParams = [...params];

    // Add order by and direction
    sql += ` ORDER BY ${orderBy} ${orderDirection}`;

    // Add pagination
    const offset = (page - 1) * pageSize;
    sql += " LIMIT ? OFFSET ?";
    params.push(pageSize, offset);

    getDbInstance().get(
      countSql,
      countParams,
      (countErr, { total = 0 } = {}) => {
        if (countErr) {
          console.error("Failed to count user downloads:", countErr);
          callback({ total, data: [], page, pageSize });
          return;
        }

        getDbInstance().all(sql, params, (err, rows) => {
          if (err) {
            console.error("Failed to fetch user downloads:", err);
            callback({ total, data: [], page, pageSize });
          } else {
            const downloads = rows.map((row) => {
              row.playload = JSON.parse(row.playload);
              return row;
            });

            callback({ total, data: downloads, page, pageSize });
          }
        });
      }
    );
  });
};

// 获取数据库中所有状态和其对应的数量
const getDownloadStatusCount = ({ userid, groupDeviceId }) => {
  return new Promise((resolve, reject) => {
    getDbInstance().serialize(() => {
      let sql =
        "SELECT status, COUNT(*) as count FROM downloads WHERE userid = ? AND groupDeviceId = ? GROUP BY status";
      let params = [userid, groupDeviceId];

      if (!groupDeviceId) {
        sql = sql.replace(" AND groupDeviceId = ?", "");
        params = [userid];
      }

      getDbInstance().all(sql, params, (err, rows) => {
        if (err) {
          console.error(err);
          reject(err);
        } else {
          const statusCount = {};
          rows.forEach((row) => {
            statusCount[row.status] = row.count;
          });
          resolve(statusCount);
        }
      });
    });
  });
};

// 根据一个或多个taskid删除下载条目
const deleteDownloadByTaskIds = (taskids, callback) => {
  if (!Array.isArray(taskids) || taskids.length === 0) {
    callback(new Error("Invalid taskids provided. Must be a non-empty array."));
    return;
  }

  const placeholders = taskids.map(() => "?").join(",");
  const sql = `DELETE FROM downloads WHERE taskid IN (${placeholders})`;

  getDbInstance().run(sql, taskids, (err) => {
    if (err) {
      console.error("Failed to delete download(s) by taskid(s):", err);
      callback(err);
    } else {
      callback(null);
    }
  });
};

// 插入新的下载条目
const insertDownloadItem = (downloadData, callback) => {
  const stmt = getDbInstance().prepare(`
      INSERT INTO downloads (
        taskid,
        userid,
        status,
        fileName,
        fileSize,
        filePath,
        fileOwner,
        fileRemotePath,
        fileMimeType,
        fileType,
        progress,
        localDirectory,
        downloadDate,
        completedDate,
        groupDeviceId,
        deviceId,
        hostname,
        syKeyDid,
        syKeyInitString,
        syKeyApiLicenses,
        syKeyCrcKey,
        playload,
        code
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

  stmt.run(
    [
      downloadData.taskid,
      downloadData.userid,
      downloadData.status,
      downloadData.fileName,
      downloadData.fileSize,
      downloadData.filePath,
      downloadData.fileOwner,
      downloadData.fileRemotePath,
      downloadData.fileMimeType,
      downloadData.fileType,
      downloadData.progress,
      downloadData.localDirectory,
      downloadData.downloadDate,
      downloadData.completedDate,
      downloadData.groupDeviceId,
      downloadData.deviceId,
      downloadData.hostname,
      downloadData.syKeyDid,
      downloadData.syKeyInitString,
      downloadData.syKeyApiLicenses,
      downloadData.syKeyCrcKey,
      JSON.stringify(downloadData.playload),
      downloadData.code,
    ],
    callback
  );

  stmt.finalize();
};

// 插入新的下载条目 - 批量
const insertDownloadItems = (downloadDataArray, callback) => {
  // const length = downloadDataArray.length;
  getDbInstance().serialize(() => {
    getDbInstance().run("BEGIN TRANSACTION"); // 开始事务

    const stmt = getDbInstance().prepare(`
      INSERT INTO downloads (
        taskid,
        userid,
        status,
        fileName,
        fileSize,
        filePath,
        fileOwner,
        fileRemotePath,
        fileMimeType,
        fileType,
        progress,
        localDirectory,
        downloadDate,
        completedDate,
        groupDeviceId,
        deviceId,
        hostname,
        syKeyDid,
        syKeyInitString,
        syKeyApiLicenses,
        syKeyCrcKey,
        playload,
        code
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    downloadDataArray.forEach((downloadData) => {
      stmt.run(
        downloadData.taskid,
        downloadData.userid,
        downloadData.status,
        downloadData.fileName,
        downloadData.fileSize,
        downloadData.filePath,
        downloadData.fileOwner,
        downloadData.fileRemotePath,
        downloadData.fileMimeType,
        downloadData.fileType,
        downloadData.progress,
        downloadData.localDirectory,
        downloadData.downloadDate,
        downloadData.completedDate,
        downloadData.groupDeviceId,
        downloadData.deviceId,
        downloadData.hostname,
        downloadData.syKeyDid,
        downloadData.syKeyInitString,
        downloadData.syKeyApiLicenses,
        downloadData.syKeyCrcKey,
        JSON.stringify(downloadData.playload),
        downloadData.code
      );
    });

    stmt.finalize();
    getDbInstance().run("COMMIT", callback); // 提交事务并调用回调函数
  });
};

// 更新下载条目的状态
const updateDownloadStatus = (
  {
    taskid,
    status,
    completedDate = Date.now(),
    progress = 0,
    filePath,
    code = 0,
  },
  callback = () => {}
) => {
  getDbInstance().serialize(() => {
    let sql =
      "UPDATE downloads SET status = ?, completedDate = ?, progress = ?, code = ?";
    const params = [status, completedDate, progress, code, taskid];

    if (typeof filePath !== "undefined") {
      sql += ", filePath = ?";
      params.splice(4, 0, filePath); // 插入 filePath 到第四个位置
    }

    sql += " WHERE taskid = ?";

    getDbInstance().run(sql, params, callback);
  });
};

// 更新下载的目录

const updateDownloadDir = (
  { taskid, filePath, localDirectory },
  callback = () => {}
) => {
  getDbInstance().serialize(() => {
    const sql =
      "UPDATE downloads SET filePath = ?, localDirectory = ? WHERE taskid = ?";
    const params = [filePath, localDirectory, taskid];
    getDbInstance().run(sql, params, callback);
  });
};

// 批量更新下载条目的状态
const updateDownloadStatusBulk = (updates, callback) => {
  getDbInstance().serialize(() => {
    getDbInstance().run("BEGIN TRANSACTION");
    updates.forEach(({ taskid, status, completedDate, progress, code }) => {
      getDbInstance().run(
        "UPDATE downloads SET status = ?, completedDate = ?, progress = ?, code = ? WHERE taskid = ?",
        [status, completedDate, progress, code, taskid],
        (err) => {
          if (err) {
            getDbInstance().run("ROLLBACK");
            callback(err);
          }
        }
      );
    });
    getDbInstance().run("COMMIT", callback);
  });
};

// 使用getDbInstance().serialize 优化性能
// const updateAllDownloadStatus = ({userid, groupDeviceId, oldStatuses = [], newStatus}) => {
//   return new Promise((resolve, reject) => {
//     getDbInstance().serialize(() => {
//       getDbInstance().run('BEGIN TRANSACTION');

//       const placeholders = oldStatuses.map(() => '?').join(',');
//       const sql = `
//         UPDATE downloads
//           SET status = ?
//           WHERE status IN (${placeholders})
//           AND userid = ?
//           AND groupDeviceId = ?`;

//       const params = [newStatus, ...oldStatuses, userid, groupDeviceId];

//       getDbInstance().run(sql, params, (error) => {
//         if (error) {
//           getDbInstance().run('ROLLBACK;');
//           reject(error);
//         } else {
//           getDbInstance().run('COMMIT;');
//           resolve();
//         }
//       });
//     });
//   });
// };
const updateAllDownloadStatus = ({
  userid,
  groupDeviceId,
  oldStatuses = [],
  newStatus,
  code = 3,
}) => {
  return new Promise((resolve, reject) => {
    getDbInstance().serialize(() => {
      const placeholders = oldStatuses.map(() => "?").join(",");

      // First, get the IDs of all the rows we want to update
      let selectSql = `
      SELECT taskid
      FROM downloads 
      WHERE status IN (${placeholders})
      AND userid = ?
      AND groupDeviceId = ?`;

      let params = [...oldStatuses, userid, groupDeviceId];

      if (!groupDeviceId) {
        selectSql = selectSql.replace("AND groupDeviceId = ?", "");
        params = [...oldStatuses, userid];
      }
      getDbInstance().all(selectSql, params, (error, rows) => {
        if (error) {
          reject(error);
        } else {
          const idsToUpdate = rows.map((row) => row.taskid);

          const updateBatch = () => {
            if (idsToUpdate.length === 0) {
              resolve();
              return;
            }

            // Take the next 1000 IDs
            const batchIds = idsToUpdate.splice(
              0,
              Math.min(1000, idsToUpdate.length)
            );
            const idPlaceholders = batchIds.map(() => "?").join(",");

            const updateSql = `
            UPDATE downloads
            SET status = ?, code = ?
            WHERE taskid IN (${idPlaceholders})`;

            const params = [newStatus, code, ...batchIds];

            getDbInstance().run(updateSql, params, (error) => {
              if (error) {
                reject(error);
              } else {
                updateBatch(); // If there are more rows to update, do another batch
              }
            });
          };

          updateBatch(); // Start the first batch
        }
      });
    });
  });
};

const deleteAllDownloadStatus = ({
  userid,
  groupDeviceId,
  statusesToDelete = [],
}) => {
  return new Promise((resolve, reject) => {
    getDbInstance().serialize(() => {
      const placeholders = statusesToDelete.map(() => "?").join(",");

      let deleteSql = `
      DELETE FROM downloads 
      WHERE status IN (${placeholders})
      AND userid = ?
      AND groupDeviceId = ?`;

      let params = [...statusesToDelete, userid, groupDeviceId];

      if (!groupDeviceId) {
        deleteSql = deleteSql.replace("AND groupDeviceId = ?", "");
        params = [...statusesToDelete, userid];
      }

      getDbInstance().run(deleteSql, params, (error) => {
        if (error) {
          reject(error);
        } else {
          resolve();
        }
      });
    });
  });
};

const getDownloadsByTaskids = (taskids) => {
  return new Promise((resolve, reject) => {
    // const placeholders1 = columns.map(() => '?').join(', ');
    const placeholders2 = taskids.map(() => "?").join(", ");
    const selectSql = `
      SELECT *
      FROM downloads 
      WHERE taskid IN (${placeholders2})`;
    getDbInstance().all(selectSql, [...taskids], (error, rows) => {
      if (error) {
        reject(error);
      } else {
        resolve(rows);
      }
    });
  });
};
const getDownloadByTaskid = (taskid) => {
  return new Promise((resolve, reject) => {
    const sql = "SELECT * FROM downloads WHERE taskid = ?";

    getDbInstance().get(sql, [taskid], (err, row) => {
      if (err) {
        reject(err);
      } else {
        if (row && row.playload) {
          row.playload = JSON.parse(row.playload);
        }
        resolve(row);
      }
    });
  });
};

module.exports = {
  updateDownloadDir,
  getUserDownloadsByDeviceId,
  getDownloadStatusCount,
  deleteDownloadByTaskIds,
  insertDownloadItem,
  insertDownloadItems,
  updateDownloadStatus,
  updateDownloadStatusBulk,
  updateAllDownloadStatus,
  deleteAllDownloadStatus,
  getDownloadsByTaskids,
  getDownloadByTaskid,
};
